{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "tourism_filename = '../data/oecd_tourism.csv'\n",
    "tourism_df = pd.read_csv(tourism_filename, \n",
    "                        usecols=['LOCATION', 'SUBJECT', 'TIME', 'Value'])\n",
    "\n",
    "locations_filename = '../data/oecd_locations.csv'\n",
    "locations_df = pd.read_csv(locations_filename,\n",
    "                          header=None,\n",
    "                           names=['LOCATION', 'NAME'],\n",
    "                          index_col='LOCATION')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 1\n",
    "\n",
    "What happens if we perform the join in the other direction?  That is, if we invoke `join` on `tourism_df`, passing it an argument of `locations_df`?  Do we get the same result?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUBJECT</th>\n",
       "      <th>TIME</th>\n",
       "      <th>Value</th>\n",
       "      <th>NAME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LOCATION</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2008</td>\n",
       "      <td>31159.800</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2009</td>\n",
       "      <td>29980.700</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2010</td>\n",
       "      <td>35165.500</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2011</td>\n",
       "      <td>38710.100</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2012</td>\n",
       "      <td>38003.700</td>\n",
       "      <td>Australia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZAF</th>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2015</td>\n",
       "      <td>5734.731</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZAF</th>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2016</td>\n",
       "      <td>5354.391</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZAF</th>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2017</td>\n",
       "      <td>6067.963</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZAF</th>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2018</td>\n",
       "      <td>6347.762</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZAF</th>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2019</td>\n",
       "      <td>5866.453</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1234 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          SUBJECT  TIME      Value       NAME\n",
       "LOCATION                                     \n",
       "AUS       INT_REC  2008  31159.800  Australia\n",
       "AUS       INT_REC  2009  29980.700  Australia\n",
       "AUS       INT_REC  2010  35165.500  Australia\n",
       "AUS       INT_REC  2011  38710.100  Australia\n",
       "AUS       INT_REC  2012  38003.700  Australia\n",
       "...           ...   ...        ...        ...\n",
       "ZAF       INT-EXP  2015   5734.731        NaN\n",
       "ZAF       INT-EXP  2016   5354.391        NaN\n",
       "ZAF       INT-EXP  2017   6067.963        NaN\n",
       "ZAF       INT-EXP  2018   6347.762        NaN\n",
       "ZAF       INT-EXP  2019   5866.453        NaN\n",
       "\n",
       "[1234 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We're again performing a left join, meaning that the left side (i.e., the data frame on \n",
    "# which we're running the join) determines which rows will be included. If there is no match\n",
    "# on the right, then we get a null value in NAME.\n",
    "\n",
    "tourism_df.set_index('LOCATION').join(locations_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 2\n",
    "\n",
    "Get the mean tourism income per year, rather than by country.  Do we see any evidence of less tourism income during time of the Great Recession, which started in in 2008?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TIME\n",
       "2019    62786.617333\n",
       "2018    43185.853875\n",
       "2017    40326.702250\n",
       "2014    40043.334563\n",
       "2016    39483.592062\n",
       "2015    38912.695437\n",
       "2013    37996.198750\n",
       "2012    35628.632063\n",
       "2011    34299.966375\n",
       "2008    31757.065750\n",
       "2010    30949.524125\n",
       "2009    28505.886562\n",
       "Name: Value, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Yes, we definitely see that 2008, 2009, and 2010 are at the bottom of the list.\n",
    "\n",
    "fullname_df = locations_df.join(tourism_df.set_index('LOCATION'))\n",
    "\n",
    "(\n",
    "    fullname_df\n",
    "    .loc[fullname_df['SUBJECT'] == 'INT_REC']\n",
    "    .groupby('TIME')['Value']\n",
    "    .mean()\n",
    "    .sort_values(ascending=False)\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 3\n",
    "\n",
    "Reset the index on `locations_df`, such that it has a (default) numeric index, and two columns (`LOCATION` and `NAME`). Now run `join` on `locations_df`, specifying that you want to use the `LOCATION` column on the caller, rather than the index. (The argument data frame will always be joined on its index.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>SUBJECT</th>\n",
       "      <th>TIME</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LOCATION</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>Australia</td>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2008</td>\n",
       "      <td>31159.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>Australia</td>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2009</td>\n",
       "      <td>29980.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>Australia</td>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2010</td>\n",
       "      <td>35165.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>Australia</td>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2011</td>\n",
       "      <td>38710.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>Australia</td>\n",
       "      <td>INT_REC</td>\n",
       "      <td>2012</td>\n",
       "      <td>38003.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISR</th>\n",
       "      <td>Israel</td>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2015</td>\n",
       "      <td>7507.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISR</th>\n",
       "      <td>Israel</td>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2016</td>\n",
       "      <td>8210.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISR</th>\n",
       "      <td>Israel</td>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2017</td>\n",
       "      <td>8986.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISR</th>\n",
       "      <td>Israel</td>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2018</td>\n",
       "      <td>9974.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISR</th>\n",
       "      <td>Israel</td>\n",
       "      <td>INT-EXP</td>\n",
       "      <td>2019</td>\n",
       "      <td>10389.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>364 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME  SUBJECT  TIME    Value\n",
       "LOCATION                                   \n",
       "AUS       Australia  INT_REC  2008  31159.8\n",
       "AUS       Australia  INT_REC  2009  29980.7\n",
       "AUS       Australia  INT_REC  2010  35165.5\n",
       "AUS       Australia  INT_REC  2011  38710.1\n",
       "AUS       Australia  INT_REC  2012  38003.7\n",
       "...             ...      ...   ...      ...\n",
       "ISR          Israel  INT-EXP  2015   7507.0\n",
       "ISR          Israel  INT-EXP  2016   8210.3\n",
       "ISR          Israel  INT-EXP  2017   8986.0\n",
       "ISR          Israel  INT-EXP  2018   9974.7\n",
       "ISR          Israel  INT-EXP  2019  10389.5\n",
       "\n",
       "[364 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tourism_df = tourism_df.set_index('LOCATION')\n",
    "locations_df.join(tourism_df, on='LOCATION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
